{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Sheet1', 'Sheet2', 'Sheet3']\n",
      "3\n",
      "1\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "import openpyxl\n",
    "import os\n",
    "import torch\n",
    "import sys\n",
    "import xlrd\n",
    "\n",
    "\n",
    "def cell_featurization_1(file_path):\n",
    "    book = xlrd.open_workbook(file_path, formatting_info=True)\n",
    "    print(book.sheet_names())\n",
    "    ss = book.sheets()[1]\n",
    "    print(book.nsheets)\n",
    "    sh = book.sheet_by_index(0)\n",
    "    xf_list = book.xf_list\n",
    "    xfx = sh.cell_xf_index(0, 0)\n",
    "    cell_xf = xf_list[xfx]\n",
    "    print(cell_xf.protection.cell_locked)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    fname = r\"C:\\Users\\zhangbohang\\Projects\\TableSense\\test_data\\1_Book1.xls\"\n",
    "\n",
    "    cell_featurization_1(fname)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import openpyxl\n",
    "import os\n",
    "import sys\n",
    "\n",
    "\n",
    "class ExcelFile(object):\n",
    "    def __init__(self):\n",
    "        self.name = \"\"  # 文件名\n",
    "        self.ext = \"\"  # 扩展\n",
    "        self.sheet_num = 0\n",
    "        self.sheets = []\n",
    "\n",
    "\n",
    "class Sheet(object):\n",
    "    def __init__(self):\n",
    "        self.sheet_id = 0  # sheet id\n",
    "        self.sheet_name = \"\"  # sheet 名\n",
    "        self.first_row_num = 0  # 第一行行号\n",
    "        self.last_row_num = 0  # 第一列列号\n",
    "        self.first_col_num = 0\n",
    "        self.last_col_num = 0\n",
    "        self.all_cell_count = 0  # 所有单元格的数量\n",
    "        self.frozen_row = 0  # 冻结单元格行号\n",
    "        self.frozen_col = 0  # 冻结单元格列号\n",
    "        self.is_freeze = False  # 是否有冻结单元格\n",
    "\n",
    "\n",
    "class Cell(object):\n",
    "    def __init__(self):\n",
    "        self.col_id = 0  # 列 id\n",
    "        self.row_id = 0  # 行 id\n",
    "        self.col_name = \"\"  # 列名\n",
    "        self.row_name = \"\"  # 行名\n",
    "        self.width = 0  # 宽度\n",
    "        self.height = 0  # 高度\n",
    "        self.have_left_border = False  # 是否有左边框\n",
    "        self.have_top_border = False  # 是否有上边框\n",
    "        self.have_right_border = False  # 是否有右边框\n",
    "        self.have_bottom_border = False  # 是否有下边框\n",
    "        self.left_border_type = 1  # 左边框类型\n",
    "        self.left_border_color = \"\"  # 左边框颜色\n",
    "        self.top_border_type = 1  # 上边框类型\n",
    "        self.top_border_color = \"\"  # 上边框颜色\n",
    "        self.right_border_type = 1  # 右边框类型\n",
    "        self.right_border_color = \"\"  # 右边框颜色\n",
    "        self.bottom_border_type = 1  # 下边框类型\n",
    "        self.bottom_border_color = \"\"  # 下边框颜色\n",
    "        self.is_non_empty = True  # 是否有内容\n",
    "        self.is_fomular = False  # 是否是公式\n",
    "        self.data = \"\"  # 单元格内容|\n",
    "        self.data_type = \"\"  # 单元格内容的类型\n",
    "        self.have_background = False  # 是否有背景填充\n",
    "        self.background_color = \"\"  # 北京填充颜色\n",
    "        self.font_family = \"\"  # 字体\n",
    "        self.font_style = 0  # 字形\n",
    "        self.font_size = 9  # 字号\n",
    "        self.under_line_type = 0  # 下划线类型\n",
    "        self.have_delete_line = False  # 是否有删除线\n",
    "        self.superscript = False  # 是否为上标\n",
    "        self.subscript = False  # 是否为下表\n",
    "        self.font_color = \"\"  # 字的颜色\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OneHotEncoder(categories=[['Arial', 'Times New Roman', 'Microsoft YaHei Bold',\n",
      "                           'ARIAL', 'Microsoft YaHei', '等线', '华文细黑', '宋体',\n",
      "                           'Arial Unicode MS']],\n",
      "              handle_unknown='ignore')\n",
      "OneHotEncoder(categories=[['justify', 'centerContinuous', 'right', 'center',\n",
      "                           'general', 'fill', 'distributed', 'left']],\n",
      "              handle_unknown='ignore')\n"
     ]
    }
   ],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder\n",
    "\n",
    "fontname_enc = OneHotEncoder(categories=[[\n",
    "    'Arial', 'Times New Roman', 'Microsoft YaHei Bold', 'ARIAL',\n",
    "    'Microsoft YaHei', '等线', '华文细黑', '宋体', 'Arial Unicode MS'\n",
    "]],\n",
    "                             handle_unknown='ignore')\n",
    "alignh_enc = OneHotEncoder(categories=[[\n",
    "    'justify', 'centerContinuous', 'right', 'center', 'general', 'fill',\n",
    "    'distributed', 'left'\n",
    "]],\n",
    "                           handle_unknown='ignore')\n",
    "alignv_enc = OneHotEncoder(\n",
    "    categories=[['justify', 'center', 'top', 'bottom', 'distributed']],\n",
    "    handle_unknown='ignore')\n",
    "color_enc = OneHotEncoder(categories=[[0, 1, 2, 3], [0, 1, 2, 3], [0, 1, 2,\n",
    "                                                                   3]],\n",
    "                          handle_unknown='ignore')\n",
    "border_enc = OneHotEncoder(categories=[[None, 'thin', 'medium']],\n",
    "                           handle_unknown='ignore')\n",
    "print(fontname_enc)\n",
    "print(alignh_enc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0. 1. 0.]]\n"
     ]
    }
   ],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder, OrdinalEncoder\n",
    "\n",
    "enc = OrdinalEncoder()\n",
    "X = [['male', 'from US', 'uses Safari'],\n",
    "     ['female', 'from Europe', 'uses Firefox']]\n",
    "enc.fit(X)\n",
    "print(enc.transform([['female', 'from US', 'uses Firefox']]))  # [[0. 1. 1.]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[array(['female', 'male'], dtype=object), array(['from Europe', 'from US'], dtype=object), array(['uses Firefox', 'uses Safari'], dtype=object)]\n",
      "['x0_female' 'x0_male' 'x1_from Europe' 'x1_from US' 'x2_uses Firefox'\n",
      " 'x2_uses Safari']\n",
      "[[1. 0. 0. 1. 0. 1.]\n",
      " [0. 1. 1. 0. 0. 1.]]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'\\n[[1. 0. 0. 1. 0. 1.]\\n [0. 1. 1. 0. 0. 1.]]\\n'"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder, OrdinalEncoder\n",
    "\n",
    "enc = OneHotEncoder()\n",
    "X = [['male', 'from US', 'uses Safari'],\n",
    "     ['female', 'from Europe', 'uses Firefox']]\n",
    "enc.fit(X)\n",
    "print(enc.categories_)\n",
    "\"\"\"[array(['female', 'male'], dtype=object), array(['from Europe', 'from US'], dtype=object), array(['uses Firefox', 'uses Safari'], dtype=object)]\"\"\"\n",
    "print(enc.get_feature_names_out())\n",
    "\"\"\"['x0_female' 'x0_male' 'x1_from Europe' 'x1_from US' 'x2_uses Firefox' 'x2_uses Safari']\"\"\"\n",
    "print(\n",
    "    enc.transform([['female', 'from US', 'uses Safari'],\n",
    "                   ['male', 'from Europe', 'uses Safari']]).toarray())\n",
    "\"\"\"\n",
    "[[1. 0. 0. 1. 0. 1.]\n",
    " [0. 1. 1. 0. 0. 1.]]\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Arial', 'Times New Roman', 'Microsoft YaHei Bold', 'ARIAL', 'Microsoft YaHei', '等线', '华文细黑', '宋体', 'Arial Unicode MS']]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fontname_enc = OneHotEncoder(categories=[[\n",
    "    'Arial', 'Times New Roman', 'Microsoft YaHei Bold', 'ARIAL',\n",
    "    'Microsoft YaHei', '等线', '华文细黑', '宋体', 'Arial Unicode MS'\n",
    "]],handle_unknown='ignore')\n",
    "\n",
    "print(fontname_enc.categories)\n",
    "fontname_enc.fit_transform([['宋体']]).toarray().tolist()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "2733babc0711d44ce5efad954458b35776c6433580c3fe472215f0f10b3611a5"
  },
  "kernelspec": {
   "display_name": "Python 3.7.8 64-bit ('venv': venv)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
